from tkinter import *
from tkinter import ttk
from threading import Thread
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from sql import sqlseverDB
from config import *
import tkinter.messagebox as messagebox
import openpyxl,time,concurrent.futures

class Data8Fra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        DC2Frame(self).pack(fill=X,padx=2,pady=2)
        RCFrame(self).pack(fill=X,padx=2,pady=2)

class DC2Frame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt1=IntVar()
        self.statustxt2=IntVar()
        self.kwRcCount=IntVar()
        Thread(target=self.getcount).start()
        self.createWidgets()
    def createWidgets(self):

        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=5)
        Label(self, text='二次铺货产品导出', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f0=Frame(self)
        f0.pack(fill=X,pady=2,padx=2)

        self.dc2btn=Button(f0,text='二次铺货导出',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green',width=20)
        self.dc2btn.pack(side=LEFT, padx=2, pady=2)
        Label(f0, textvariable=self.statustxt1,fg='red',width=2,justify='left').pack(side=LEFT, pady=2)



        self.btn_zzdc2=Button(f0,text='重置二次铺货链接',command=lambda:Thread(target=self.row_back_dc2).start(),fg='#ffffff',bg='green',width=20)
        self.btn_zzdc2.pack(side=LEFT, padx=2, pady=2)
        Label(f0, textvariable=self.statustxt2,fg='red',width=3,justify='left').pack(side=LEFT, pady=2)

        DCALLData(f0).pack(side=LEFT)

    def getcount(self):
        def get000(j,cursql):
                rs=self.ms.ExecQuery(cursql)         
                return (j,rs[0][0])

        while True:
            sqlstr='''
                    SELECT count(DISTINCT itemid) FROM ProShopeePh where 
                    daochu>0 and isqq=0 and daochu2=0 and yndes is not null and newmaxprice is not null and pmbid is not null
                '''

            sqlstr4='''
                    SELECT count(DISTINCT itemid) from ProShopeePh where
                    yndes is not null and isqq=0 and aliurl is not null
                    and daochu2>0
                    '''
            sqls=[sqlstr,sqlstr4]

            try:
                with concurrent.futures.ThreadPoolExecutor(max_workers=2) as executor:

                    futures=[executor.submit(get000,j,sqls[j-1]) for j in range(1,len(sqls)+1)]
                    for future in concurrent.futures.as_completed(futures):
                        try:
                            curindj,curcount = future.result()
                            getattr(self, f'statustxt{curindj}').set(curcount)
                        except Exception as exc:
                            print(f"线程出错: {exc}")                       

                time.sleep(10)
            except Exception as e:
                print(e)
    
    def start_dc(self):

        if not messagebox.askyesno('提示','是否二次导出?'):
            return
        
        self.dc2btn.config(state=DISABLED)
        pro_dict=getpro2()
        dc_exceldata2(pro_dict)
        print('全部二次导出完毕')
        messagebox.showinfo('提示','全部二次导出完毕')
        self.dc2btn.config(state=NORMAL)
    



    def row_back_dc2(self):

        if not messagebox.askyesno('提示','确定重置二次铺货链接状态?'):
            return
        
        self.btn_zzdc2.config(state=DISABLED)

        sqlstr='''update ProShopeePh set daochu2=0 where yndes is not null and isqq=0 and aliurl is not null and daochu2>0'''

        aff=trydosql(self.ms,sqlstr,type=2)

        print(f'重置了 {aff} 个 SKU')
        messagebox.showinfo('提示',f'重置了 {aff} 个 SKU')

        self.btn_zzdc2.config(state=NORMAL)

class RCFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.kwRcCount=IntVar()
        self.statustxt1=IntVar()
        Thread(target=self.getcount).start()
        self.createWidgets()

    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='入仓功能', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f00=Frame(f0)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, text='商家编码:').pack(side=LEFT, padx=2, pady=2)
        self.SjbmIpt = Entry(f00,width=10)
        self.SjbmIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00, text='导出数量:').pack(side=LEFT, padx=2, pady=2)
        self.DcCountIpt = Entry(f00,width=5)
        self.DcCountIpt.pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(fill=X,padx=2,pady=2)
        self.dcrcbtn=Button(f01,text='不侵权可入仓导出',command=lambda:Thread(target=self.start_dcrc).start(),fg='#ffffff',bg='green',width=20)
        self.dcrcbtn.pack(side=LEFT, padx=2, pady=2)
        Label(f01, textvariable=self.statustxt1,fg='red',width=2,justify='left').pack(side=LEFT, pady=2)
        Label(f01, text='印尼关键词:').pack(side=LEFT, padx=2, pady=2)
        self.ynkwIpt = Entry(f01)
        self.ynkwIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f01, textvariable=self.kwRcCount,fg='red',width=2,justify='left').pack(side=LEFT, pady=2)
        self.btncat=Button(f01,text='查询关键词入仓数量',fg='#ffffff',bg='green',command=lambda:Thread(target=self.cat_rccount).start())
        self.btncat.pack(side=LEFT,padx=2, pady=2)
        self.btnkwrcdc=Button(f01,text='导出',fg='#ffffff',bg='green',command=lambda:Thread(target=self.start_kwrcdc).start())
        self.btnkwrcdc.pack(side=LEFT,padx=2, pady=2)

    def getcount(self):
        def get000(j,cursql):
                rs=self.ms.ExecQuery(cursql)         
                return (j,rs[0][0])

        while True:


            sqlstr2='''
                    SELECT count(DISTINCT itemid) FROM ProShopeePh where 
                    isqq=1 and daochu3=0 and zlorder2=1 
                '''

            sqls=[sqlstr2]

            try:
                with concurrent.futures.ThreadPoolExecutor(max_workers=1) as executor:

                    futures=[executor.submit(get000,j,sqls[j-1]) for j in range(1,len(sqls)+1)]
                    for future in concurrent.futures.as_completed(futures):
                        try:
                            curindj,curcount = future.result()
                            getattr(self, f'statustxt{curindj}').set(curcount)
                        except Exception as exc:
                            print(f"线程出错: {exc}")                       

                time.sleep(10)
            except Exception as e:
                print(e)

    def start_dcrc(self):
        
        sjbm=self.SjbmIpt.get().strip()
        dcCount=self.DcCountIpt.get().strip()
        if not sjbm or not dcCount:
            messagebox.showinfo('提示','商家编码和数量不能为空')
            return
        if not messagebox.askyesno('提示','确定导出入仓产品?'):
            return
        
        dcCount=int(dcCount)
        self.dcrcbtn.config(state=DISABLED)
        pro_dict=rcpro(sjbm,dcCount)

        dc_exceldata2(pro_dict,status=1)
        print('入仓导出完毕')
        messagebox.showinfo('提示','入仓导出完毕')
        self.dcrcbtn.config(state=NORMAL)


    def cat_rccount(self):

        sjbm=self.SjbmIpt.get().strip()
        ynkw=self.ynkwIpt.get().strip()
        if not sjbm or not ynkw:
            messagebox.showinfo('提示','商家编码和印尼关键词不能为空')
            return

        self.btncat.config(state=DISABLED)
        self.kwsjbm=sjbm
        sqlstr='select * from ProShopeePh where isqq=1 and iscjrcurl=0 and daochu3=0 order by pmbid,itemid,skuPirce desc'
        print(f'正在链接数据库查询:关键词《{ynkw}》 的可入仓产品数...')
        while True:
            try:
                rp=ms.ExecQuerydict(sqlstr)
                break
            except Exception as e:
                pass
        
        ynkwpro=[]
        itemids=[]
        for r in rp:
            if ynkw in r['yntitle']:
                itemids.append(r['itemid'])
                ynkwpro.append(r)
        self.kwRcCount.set(len(set(itemids)))
        self.ynkwpro=ynkwpro
        print(f'关键词《{ynkw}》共查询到 {len(set(itemids))} 个可入仓产品')
        messagebox.showinfo('提示',f'关键词《{ynkw}》共查询到 {len(set(itemids))} 个可入仓产品')

        self.btncat.config(state=NORMAL)
    
    def start_kwrcdc(self):
        
        if not messagebox.askyesno('提示','导出不侵权可入仓?'):
            return
        
        self.dcrcbtn.config(state=DISABLED)
        pro_dict=getpro3(self.ynkwpro,self.kwsjbm)
        dc_exceldata2(pro_dict,status=1)
        print('不侵权可入仓导出完毕')
        self.kwRcCount.set(0)
        messagebox.showinfo('提示','不侵权可入仓导出完毕')
        self.dcrcbtn.config(state=NORMAL)

class DCALLData(Frame):

    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.bmCount=IntVar()
        self.rbmCount=IntVar()
        self.createWidgets()

    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X)

        f00=Frame(f0)
        f00.pack(fill=X)

        Label(f00, text='选择数据表:').pack(side=LEFT, padx=4, pady=2)
        self.dbnameSelect = ttk.Combobox(f00,state='readonly')
        self.dbnameSelect['value'] = [v for v in DBOSDICT.keys()]
        self.dbnameSelect.pack(side=LEFT, padx=5, pady=2)
        self.dbnameSelect.current(0)

        self.dcbtn=Button(f00,text='导出完整数据链',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green',width=20)
        self.dcbtn.pack(side=LEFT, padx=2, pady=2)
    
    def start_dc(self):

        self.dcbtn.config(state=DISABLED)
        cur_dbname=DBOSDICT[self.dbnameSelect.get()]
        orderstr=''
        if cur_dbname=='AllShop':
            orderstr=' order by pcount desc'

        sqlstr=f'select top 100 * from [{cur_dbname}]{orderstr}'

        fqz=self.dbnameSelect.get()
        cur_map=DBO_MAP.get(cur_dbname)
        fname=sqlToExcel(fqz,self.ms,sqlstr,dbpmap=cur_map)

        if fname:
            print(f'成功导出:《{fname}》')
            messagebox.showinfo('提示',f'成功导出:《{fname}》')
        
        else:
            print('导出失败')
        self.dcbtn.config(state=NORMAL)

# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('ADMIN3')
# Data8Fra(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()